package com.landis.temple.mobile;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;

/**
 * Local sqlite database
 * 
 * @author John Landis 
 * @author Anton Kronaj
 *
 */

public class TwitterStoryDatabase
{
    private static final String TAG = "TwitterStoryDatabase";
    
	private DatabaseHelper dbHelper;
	private SQLiteDatabase db;
	private Context context;
	
	private static final String DATABASE_TITLE = "twitter_story";
	private static final int DATABASE_VERSION = 1;
	
	// Table names
	
	private static final String STORY_TABLE = "stories";
	private static final String CHAPTER_TABLE = "chapters";
	private static final String AUTHOR_TABLE = "authors";
	
	// Story table columns
	
	public static final String STORY_ID = "_id";
	public static final String STORY_AUTHOR_ID = "stories_authors_id";
	public static final String STORY_TITLE = "stories_title";
	public static final String STORY_DATE_CREATED = "stories_date_created";
	public static final String STORY_DATE_FINISHED = "stories_date_finished";
	
	// Chapter table columns
	
	public static final String CHAPTER_ID = "_id";
	public static final String CHAPTER_STORY_ID = "chapters_stories_id";
	public static final String CHAPTER_AUTHOR_ID = "chapters_authors_id";
	public static final String CHAPTER_DATE_CREATED = "chapters_date_created";
	public static final String CHAPTER_TEXT = "chapters_text";
	
	// Author table columns
	
	public static final String AUTHOR_ID = "_id";
	public static final String AUTHOR_NAME = "authors_name";
	
	// SQL creation statements
	
	private static final String CREATE_AUTHORS =  "CREATE TABLE " + AUTHOR_TABLE + " ( " +
												  AUTHOR_ID + " INTEGER PRIMARY KEY AUTOINCREMENT , " +
												  AUTHOR_NAME + " TEXT UNIQUE NOT NULL);";
	
	private static final String CREATE_CHAPTERS = "CREATE TABLE " + CHAPTER_TABLE + " ( " +
												  CHAPTER_ID + " INTEGER PRIMARY KEY AUTOINCREMENT ," +
												  CHAPTER_STORY_ID + " INTEGER UNSIGNED NOT NULL," +
												  CHAPTER_AUTHOR_ID + " INTEGER UNSIGNED NOT NULL," +
												  CHAPTER_DATE_CREATED + " DATE NOT NULL DEFAULT ( CURRENT_TIMESTAMP ) ," +
												  CHAPTER_TEXT + " TEXT NOT NULL," +
												  "FOREIGN KEY(" + CHAPTER_STORY_ID + ") REFERENCES " + 
												  STORY_TABLE + "(" + STORY_ID + ")," +
												  "FOREIGN KEY (" + CHAPTER_AUTHOR_ID + ") REFERENCES " + 
												  AUTHOR_TABLE + "(" + AUTHOR_ID + "));";
	
	private static final String CREATE_STORIES = "CREATE TABLE " + STORY_TABLE + " ( " +
	  											  STORY_ID + " INTEGER PRIMARY KEY AUTOINCREMENT," +
	  											  STORY_AUTHOR_ID + " INTEGER UNSIGNED NOT NULL," +
	  											  STORY_TITLE + " TEXT NOT NULL," +
	  											  STORY_DATE_CREATED + " DATE NOT NULL DEFAULT ( CURRENT_TIMESTAMP ) ," +
	  											  STORY_DATE_FINISHED + " DATE DEFAULT NULL ," + 
	  											  "FOREIGN KEY (" + STORY_AUTHOR_ID + ") REFERENCES " + 
	  											  AUTHOR_TABLE + "(" + AUTHOR_ID + "));";
	
	// Helper class to create and upgrade the database
	
	private class DatabaseHelper extends SQLiteOpenHelper 
	{		
		public DatabaseHelper( Context context) 
		{
			super( context , DATABASE_TITLE , null, DATABASE_VERSION );
		}

		@Override
		public void onCreate(SQLiteDatabase database) 
		{
			database.execSQL(CREATE_AUTHORS);
			Log.d("TwitterStoryDatabase" , "Creating " + CREATE_AUTHORS );
			database.execSQL(CREATE_STORIES);
	        Log.d("TwitterStoryDatabase" , "Creating " + CREATE_STORIES );
			database.execSQL(CREATE_CHAPTERS);
	        Log.d("TwitterStoryDatabase" , "Creating " + CREATE_CHAPTERS );
		}

		@Override
		public void onUpgrade(SQLiteDatabase database, int oldVersion, int newVersion) 
		{
			Log.w( 	this.getClass().getName() 				, 
					"Upgrading database from version " 
					+ oldVersion 
					+ " to " 
					+ newVersion 
					+ ", which will destroy all old data"	);
			
			database.execSQL( "DROP TABLE IF EXISTS " + AUTHOR_TABLE  + ";" );
			database.execSQL( "DROP TABLE IF EXISTS " + CHAPTER_TABLE + ";" );
			database.execSQL( "DROP TABLE IF EXISTS " + STORY_TABLE   + ";" );

			onCreate(database);
		}
	}
	
	// Constructor
	
	public TwitterStoryDatabase( Context context )
	{
		this.context = context;
	}
	
	// Open , Close , and Upgrade functions
	
	public TwitterStoryDatabase open() throws SQLException
	{
		dbHelper = new DatabaseHelper( context );
		db = dbHelper.getWritableDatabase();

		return this;
	}

	public void close()
	{
			dbHelper.close();
	}
	
	public void upgrade( int oldVersion , int newVersion)
    {
        dbHelper.onUpgrade( db , oldVersion, newVersion );
    }
	
	// Add Functions
	
	/**
	 * Creates a new author
	 * 
	 * @param name - String author name
	 * @return long - new author primary key
	 */
	public long createAuthor( String name )
	{
	    ContentValues author = new ContentValues();
		author.put( AUTHOR_NAME , name );
		
		return db.insert( AUTHOR_TABLE , null , author );
	}
	
	/**
	 * Creates a new story associated with an author
	 * 
	 * @param author_id - long author primary key
	 * @param title - string story title
	 * @return long new story primary key
	 */
	public long createStory( long author_id , String title )
	{
	    ContentValues story = new ContentValues();		
		story.put( STORY_AUTHOR_ID , author_id );
		story.put( STORY_TITLE     , title     );
		
		return db.insert( STORY_TABLE , null , story );
	}
	
	/**
	 * Creates a new chapter associated with a story and author
	 * 
	 * @param story_id - long story primary key
	 * @param author_id - long author primary key
	 * @param text - string 
	 * @return long new chapter primary key
	 */
	public long createChapter( long story_id , long author_id , String text )
	{
	    ContentValues chapter = new ContentValues();
		
		chapter.put( CHAPTER_STORY_ID  , story_id  );
		chapter.put( CHAPTER_AUTHOR_ID , author_id );
		chapter.put( CHAPTER_TEXT      , text      );
		
		return db.insert( CHAPTER_TABLE , null , chapter );
	}
	
	// Get Functions
	
	/**
	 * Search for chapters on story primary key
	 * 
	 * @param story_id is the stories table primary key
	 * @return Cursor { long _id , long chapters_authors_id , date chapters_date_created , string chapters_text }
	 */
	public Cursor getChapters( long story_id )
	{	
		Cursor results =  db.query( 
				CHAPTER_TABLE 					     ,  // Table
				new String[] {  CHAPTER_ID           , 
				                CHAPTER_AUTHOR_ID    , 
				                CHAPTER_DATE_CREATED , 
				                CHAPTER_TEXT         } 	,  // Return Values
				CHAPTER_STORY_ID + " = " + story_id 	,  // Selection 
				null 									,  // Selection Args
				null 									,  // Group By
				null 									,  // Having
				CHAPTER_DATE_CREATED + " ASC" 			); // Order By
		
		if( results != null )
		{
			results.moveToFirst();
		}
		
		return results;
	}
	
	/**
	 * Searches database for all authors
	 * 
	 * @return Cursor { long _id , string authors_name }
	 */
	public Cursor getAuthors( )
    {   
        Cursor results =  db.query( 
                AUTHOR_TABLE                        ,  // Table
                new String[] {  AUTHOR_ID           , 
                                AUTHOR_NAME         }  ,  // Return Values
                null                                    ,  // Selection 
                null                                    ,  // Selection Args
                null                                    ,  // Group By
                null                                    ,  // Having
                null           ); // Order By
        
        if( results != null )
        {
            results.moveToFirst();
        }
        
        return results;
    }
	
	/**
	 * Searches database for all stories
	 * 
	 * @return Cursor { long _id , string authors_name , string stories_title , date stories_date_created }
	 */
	public Cursor getAllStories()
	{
		/*Cursor results =  db.query( 
				STORY_TABLE + " JOIN " + AUTHOR_TABLE		,  // Table
				new String[] {  STORY_TABLE + "." + STORY_ID            , 
				                AUTHOR_TABLE + "." + AUTHOR_NAME         , 
				                STORY_TABLE + "." + STORY_TITLE         , 
				                STORY_TABLE + "." + STORY_DATE_CREATED  }   ,  // Return Values
				                AUTHOR_TABLE + "." + AUTHOR_ID + " = " + STORY_TABLE + "." + STORY_AUTHOR_ID       ,  // Selection 
				null 									,  // Selection Args
				null 									,  // Group By
				null 									,  // Having
				STORY_DATE_CREATED + " ASC" 			); // Order By*/
		
	    String query = "SELECT " 
            + STORY_TABLE  + "." + STORY_ID           + ", " 
            + AUTHOR_TABLE + "." + AUTHOR_NAME        + ", " 
            + STORY_TABLE  + "." + STORY_TITLE        + ", "
            + STORY_TABLE  + "." + STORY_DATE_CREATED
            + " FROM " + STORY_TABLE + " JOIN " + AUTHOR_TABLE 
            + " ON " 
            + STORY_TABLE  + "." + STORY_AUTHOR_ID + " = " 
            + AUTHOR_TABLE + "." + AUTHOR_ID;
	    
	    Log.d( TAG , "query = " + query );
	    
		Cursor results = db.rawQuery( "SELECT " 
		                       + STORY_TABLE  + "." + STORY_ID           + ", " 
		                       + AUTHOR_TABLE + "." + AUTHOR_NAME        + ", " 
		                       + STORY_TABLE  + "." + STORY_TITLE        + ", "
		                       + STORY_TABLE  + "." + STORY_DATE_CREATED
		                       + " FROM " + STORY_TABLE + " JOIN " + AUTHOR_TABLE 
		                       + " ON " 
		                       + STORY_TABLE  + "." + STORY_AUTHOR_ID + " = " 
		                       + AUTHOR_TABLE + "." + AUTHOR_ID
		                       + " ORDER BY " + STORY_DATE_CREATED + " DESC"
		                       , null );
		
		if( results != null )
		{
			results.moveToFirst();
			Log.d( TAG , "getAllStories - cursor not null" );
			Log.d( TAG , "getAllStories - size = " + results.getCount() );
			
			while (results.isAfterLast() == false) 
	        {
	            Log.d( TAG , "stories0 : " + results.getString(0));
	            Log.d( TAG , "stories1 : " + results.getString(1));
	               Log.d( TAG , "stories2 : " + results.getString(2));
	                Log.d( TAG , "stories3 : " + results.getString(3));

	            results.moveToNext();
	        }
			
			results.moveToFirst();
		}
		else
		{
			Log.d("Debugging" , "getAllStoriesReturning null");
		}
		
		return results;
	}
	
	/**
	 * Searches for a stories title 
	 * 
	 * @param story_id - long story primary key
	 * @return String - story title or null if not found
	 */
	public String getStoryTitle( long story_id )
    {
        Cursor results =  db.query(
                true                            ,  // Distinct
                STORY_TABLE                     ,  // Table
                new String[] { STORY_TITLE }    ,  // Columns
                STORY_ID + "=?"                 ,  // Selection
                new String[] { Long.toString( story_id ) } ,  // Selection Arguments
                null                            ,  // Group By
                null                            ,  // Having
                null                            ,  // Order By
                null                            ); // Limit
        
        if( results != null )
        {
            results.moveToFirst();
            return results.getString(0);
        }

        return null;
    }
	
	/**
	 * Searches for a stories authors primary key
	 * 
	 * @param long story_id - story primary key
	 * @return long - authors primary key
	 */
	public long getStoryAuthor( long story_id )
	{
	    Cursor results =  db.query(
	                true                            ,  // Distinct
	                STORY_TABLE                     ,  // Table
	                new String[] { STORY_AUTHOR_ID },  // Columns
	                STORY_ID + " = " + story_id     ,  // Selection 
	                null                            ,  // Selection Arguments
	                null                            ,  // Group By
	                null                            ,  // Having
	                null                            ,  // Order By
	                null                            ); // Limit
	        
	    if( results != null )
	    {
	        results.moveToFirst();
	        return results.getLong(0);
	    }
	    
	    return -1;
	}
	
	/**
	 * Searches for an author name by primary key
	 * 
	 * @param author_id - long
	 * @return String - author name or null if not found
	 */
	public String getAuthor( long author_id )
	{
		Cursor results =  db.query(
		        true                            ,  // Distinct
				AUTHOR_TABLE 					,  // Table
				new String[] { AUTHOR_NAME }  	,  // Columns
				AUTHOR_ID + "=?"               ,  // Selection 
				new String[] { Long.toString(author_id) }   ,  // Selection Arguments
				null 							,  // Group By
				null 							,  // Having
				null						 	,  // Order By
				null                            ); // Limit
		
		if( results != null && results.getCount() != 0 )
		{
		    results.moveToFirst();
		    return results.getString(0);
		}

		return null;
	}
	
	/**
	 * Searches for the primary key associated with author name
	 * 
	 * @param author_name - string
	 * @return long author primary key or -1 for failure
	 */
	public Long getAuthorId( String author_name )
	{
	    Cursor results =  db.query( 
				true								,
				AUTHOR_TABLE 						,  // Table
				new String[] { AUTHOR_ID }  		,  // Return Values
				AUTHOR_NAME + "=?"                  ,  // Selection 
				new String[] { author_name }		,  // Selection Args
				null 								,  // Group By
				null 								,  // Having
				null						 		,
				null                                ); // Order By
		
		if( results != null && results.getCount() != 0 )
		{
		    results.moveToFirst();
		    return results.getLong(0);
		}
		
		return -1l;
	}
	
	// Delete Functions
	
	/**
	 * Deletes a story
	 * 
	 * @param storyId - long
	 * @return boolean determined by query success
	 */
    public boolean deleteStory( long storyId ) 
    {
        return db.delete( STORY_TABLE , STORY_ID + "=" + storyId, null) > 0;
    }
    
    /**
     * Deletes a chapter
     * 
     * @param chapterId - long
     * @return boolean determined by query success
     */
    public boolean deleteChapter( long chapterId ) 
    {
        return db.delete( CHAPTER_TABLE , CHAPTER_ID + "=" + chapterId, null) > 0;
    }
    
    /**
     * Deletes all chapters associated with a story
     * 
     * @param story_Id - long
     * @return boolean determined by query success
     */
    public boolean deleteAllChapters( long story_id )
    {   
        return db.delete( CHAPTER_TABLE , CHAPTER_STORY_ID + " = ?" ,  new String[] { Long.toString( story_id ) } ) > 0;
    }
}
